var express = require('express');
var router = express.Router();

//引入封装好的Query方法
const Query = require('../utils/db.js')
//引入模块，并使用了简写方式的解构赋值
const { API_URL } = require('../utils/config.js')

/* GET home page. */
router.get('/demo', function (req, res, next) {
  res.send('这是我们的列表模块')
});
//这是我们的列表 
//各个分类接口
//1. 获取地区接口
router.get('/areas', async (req, res, next) => {
  let data = {}
  //1. 定义sql
  let sql = `SELECT * FROM e_area`
  //2. 使用引入的Query执行sql
  let { err, result } = await Query(sql)
  // res.send( err )
  if (err) {
    data = {
      msg: '稍后再试',
      status: 500,
    }
    res.send(data)
    return
  }

  data = {
    msg: 'OK',
    status: 200,
    result
  }
  res.send(data)
});
//2. 获取年级接口
router.get('/grades', async (req, res, next) => {
  let data = {}
  //1. 定义sql
  let sql = `SELECT * FROM e_grade`
  //2. 使用引入的Query执行sql
  let { err, result } = await Query(sql)
  // res.send( err )
  if (err) {
    data = {
      msg: '稍后再试',
      status: 500,
    }
    res.send(data)
    return
  }

  data = {
    msg: 'OK',
    status: 200,
    result
  }
  res.send(data)
});
//3. 获取学科接口
router.get('/subjects', async (req, res, next) => {
  let data = {}
  //1. 定义sql
  let sql = `SELECT * FROM e_subject`
  //2. 使用引入的Query执行sql
  let { err, result } = await Query(sql)
  // res.send( err )
  if (err) {
    data = {
      msg: '稍后再试',
      status: 500,
    }
    res.send(data)
    return
  }

  data = {
    msg: 'OK',
    status: 200,
    result
  }
  res.send(data)
});
//4. 获取考试类别接口
router.get('/cates', async (req, res, next) => {
  let data = {}
  //1. 定义sql
  let sql = `SELECT * FROM e_category`
  //2. 使用引入的Query执行sql
  let { err, result } = await Query(sql)
  // res.send( err )
  if (err) {
    data = {
      msg: '稍后再试',
      status: 500,
    }
    res.send(data)
    return
  }

  data = {
    msg: 'OK',
    status: 200,
    result
  }
  res.send(data)
});

//...剩下分类，自己cv就可以

//课程列表接口：很复杂
router.get('/courses', async (req, res, next) => {
  let data = {}

  /*
    page:当前页
    size:每页显示几条
    start: (page-1)*size
    rows:总条数 执行sql语句得到  不带limit的sql语句
    pageTotal:总页数   Math.ceil(rows/size)
  */
  //定义一些参数
  //  let size = 5
  //  let page = 1   如果未传则默认为page:1 size:5
  // let area = '北京'
  // let grade = '初二'
  // let subject = '语文'
  // let category = '期中'
  let { page = 1, size = 5, area = '', grade = '', subject = '', category = '' } = req.query


  //-----------------以下是分页-------------------
  page = parseInt(page)
  size = parseInt(size) //保证是个整数 。

  if (!Number(page) || !Number(size) || Number(size) == 0 || Number(page) == 0) {
    data = {
      msg: '请正确传递一个大于0的page或size',
      status: 500,
    }
    res.send(data)
    return
  }
  let start = (page - 1) * size
  let pageTotal = 0//总页码
  let rows = 0 //总条数
  //-----------------以上是分页-------------------

  //-------------------以下是筛选逻辑-------------------------
  /*
     1. 无WHERE
     2. 有WHERE
         2.1 一个条件
             WHERE 无AND
         2.2 两个及以上
             WHERE 有AND
  */
  let whereStr = `` //存放我们的where条件的。


  if (area != '') { //如果area变量不为空，意味着要按照地区筛选
    if (whereStr == '') { //代表无任何条件，则需要一个WHERE关键字
      whereStr += ` WHERE area_name = '${area}' `
    } else { //代表已经有一个条件需要AND 拼接
      whereStr += ` AND area_name = '${area}' `
    }
  }

  if (grade != '') { //如果grade变量不为空，意味着要按照年级筛选
    if (whereStr == '') {
      whereStr += ` WHERE grade_name = '${grade}' `
    } else {
      whereStr += ` AND grade_name = '${grade}' `
    }
  }

  if (subject != '') { //如果subject变量不为空，意味着要按照学科筛选
    if (whereStr == '') {
      whereStr += ` WHERE subject_name = '${subject}' `
    } else {
      whereStr += ` AND subject_name = '${subject}' `
    }
  }

  if (category != '') { //如果category变量不为空，意味着要按照阶段考试类别筛选
    if (whereStr == '') {
      whereStr += ` WHERE category_name = '${category}' `
    } else {
      whereStr += ` AND category_name = '${category}' `
    }
  }
  //-------------------以上是筛选逻辑-------------------------

  //1. 定义sql

  //此sql语句是在整个项目中是最复杂的。各分类的筛选功能，又要分页功能。

  //JWOL
  //LIMIT
  let sql1 = `SELECT * FROM e_course ${whereStr}` //总条数的sql
  // 
  // 
  // 
  let sql2 =`SELECT id,cid, CONCAT('${API_URL}/uploads/courses/',image_src) AS image_src,title,area_name,subject_name,type_name,price FROM e_course ${whereStr} ORDER BY id DESC LIMIT ${start},${size} `
  // let sql2 = ` SELECT * FROM e_course ${whereStr} ORDER BY id DESC LIMIT ${start},${size} `

  // res.send(sql2)
  // return

  //2. 使用引入的Query执行sql
  let { err: err1, result: result1 } = await Query(sql1)
  let { err: err2, result: result2 } = await Query(sql2)
  // res.send( err )
  if (err1 || err2) {
    data = {
      msg: '稍后再试',
      status: 500,
    }
    res.send(data)
    return
  }

  //err无问题，去计算 总条数和总页码
  rows = result1.length
  pageTotal = Math.ceil(rows / size)

  data = {
    msg: 'OK',
    status: 200,
    result: {
      page, //当前页码
      pageTotal, //总页码
      rows,//总条数
      data: result2
    }
  }
  res.send(data)
});

//搜索接口：独立完成
//search 
router.get('/search', async (req, res) => {
  let { keyword, pageSize = 5, page = 1 } = req.query
  page = parseInt(page)
  pageSize = parseInt(pageSize) //保证是个整数 。
  if (!Number(page) || !Number(pageSize) || Number(pageSize) == 0 || Number(page) == 0) {
    data = {
      msg: '请正确传递一个大于0的page或pageSize',
      status: 500,
    }
    res.send(data)
    return
  }
  if (!keyword) {
    data = {
      msg: '请传递关键字',
      status: 500,
    }
    res.send(data)
    return
  }
  let start = (page - 1) * pageSize
  let pageTotal = 0//总页码
  let rows = 0 //总条数
  console.log(start, pageSize);
  let sql = `SELECT * , CONCAT('${API_URL}/uploads/courses/',image_src) AS image_src  FROM e_course WHERE title LIKE '%${keyword}%' ORDER BY id DESC  LIMIT  ${start} , ${pageSize}`
  let result = await Query(sql)

  rows = result.result.length
  pageTotal = Math.ceil(rows / pageSize)
  let data = {
    msg: 'ok',
    status: 200,
    result: {
      rows,//总条数
      page, //当前页码
      pageTotal, //总页码
      keyword,
      data: result.result
    }

  }
  res.send(data)

})


module.exports = router;
